// isInit是启用账套
const tools = require("../tools/tool");
const {db, QueryTypes, Op} = require("../models/db");

const saveInventory = async (list, isBill = false, t) => {
    let uuid = tools.uuid()
    let ut = tools.getTime()
    for (let i = 0; i < list.length; i++) {
        let row = list[i]
        let transType = row.transType
        // list[i].uuid = uuid
        if(row.isInit === 0) {
            let last = list[i-1]
            list[i].seq = last.seq + 1
            if(transType === 150501 || transType === 150706) {
                list[i].lastCount = tools.Np.plus(last.lastCount, row.inCount)
                list[i].lastMoney = tools.Np.round(tools.Np.plus(last.lastMoney, row.inMoney),2)
                list[i].lastPrice = list[i].lastCount === 0 ? last.lastPrice : (tools.Np.divide(list[i].lastMoney,list[i].lastCount))
            }
            if(transType === 150502) {
                list[i].inPrice = last.lastPrice
                list[i].inMoney = tools.Np.round(tools.Np.times(row.inCount, list[i].inPrice),2)
                list[i].lastCount = tools.Np.plus(last.lastCount, row.inCount)
                list[i].lastMoney = tools.Np.round(tools.Np.plus(last.lastMoney, list[i].inMoney),2)
                list[i].lastPrice = list[i].lastCount === 0 ? last.lastPrice : (tools.Np.divide(list[i].lastMoney,list[i].lastCount))
            }
            if(transType === 150601 || transType === 150602 || transType === 150603 || transType === 150806) {
                list[i].costPrice = last.lastPrice
                list[i].costMoney = tools.Np.round(tools.Np.times(row.outCount, list[i].costPrice),2)
                list[i].lastCount = tools.Np.minus(last.lastCount, row.outCount)
                list[i].lastMoney = tools.Np.round(tools.Np.minus(last.lastMoney, list[i].costMoney),2)
                list[i].lastPrice = list[i].lastCount === 0　?(last.lastPrice):(tools.Np.divide(list[i].lastMoney, list[i].lastCount))
            }
        }
    }
    if(!isBill) {
        let page = Math.ceil(list.length / 1000)
        for (let i = 0; i < page; i++) {
            let start = i * 1000
            let end = (start + 1000) > list.length ? list.length : (start + 1000)
            let arr = list.slice(start, end)
            await db.models['inventoryDetail'].bulkCreate(arr, {transaction: t})
        }
    }
    if(isBill) {
        for (let i = 0; i < list.length; i++) {
            let row = list[i]
            if(list[i].isInit === 0) {
                let id = row.id
                delete row.id
                await db.models['inventoryDetail'].update(row,{
                    where: {
                        id
                    }
                }, {transaction: t})
            }
            // 最后一个写入主表 inventory 的lastCount,lastPrice,lastMoney中
            if(i === list.length-1) {
                let model = await db.models['inventory'].findOne({
                    where: {
                        tenantId: row.tenantId,
                        locationId: row.locationId,
                        invId: row.invId
                    }
                })
                if(!model) {
                    model = db.models['inventory'].build()
                    model.locationId = row.locationId
                    model.invId = row.invId
                    model.tenantId = row.tenantId
                }
                model.lastCount = row.lastCount
                model.lastMoney = row.lastMoney
                model.lastPrice = row.lastPrice
                await model.save({transaction: t})
            }
        }
    }
}

// 审核票据后更新库存余额
const updateInventoryByBill = async (ctx, billId, t) => {
    let tenantId = ctx.user.tenantId
    // 汇总商品更新，租户-仓库-商品-日期
    let sq = `select tenantId,locationId,invId,billDate from inventory_detail where tenantId=${tenantId} and iid=${billId} group by tenantId,locationId,invId,billDate`
    let goods = await db.query(sq, {type: QueryTypes.SELECT})
    for (let i = 0; i < goods.length; i++) {
        let row = goods[i]
        let sql = `
            select * from (
             SELECT top 1 1 isInit,* from inventory_detail where   tenantId=${row.tenantId} and locationId=${row.locationId} and invId=${row.invId} and billDate<'${row.billDate}' and isCal=1  order by seq desc 
             union all
             select  0 isInit,  *  from inventory_detail where tenantId=${row.tenantId} and locationId=${row.locationId}  and invId=${row.invId} and  billDate>='${row.billDate}' and isCal=1
            ) a
        `
        let list = await db.query(sql + ' order by tenantId,locationId,invId,billDate,transType ', {type: QueryTypes.SELECT})
        await saveInventory(list, true, t)
    }
}
// 通过商品，重算所有库存
const updateInventoryByGoods = async (ctx) => {
    // 先删除现有明细表中的商品
}
// 期初开启账套后更新库存
const updateInventoryByBeg = async (ctx) => {
    let tenantId = ctx.user.tenantId
    let ut = tools.getTime()
    await db.models['inventoryDetail'].destroy({
        where: {
            tenantId
        }
    })
    await db.models['inventory'].destroy({
        where: {
            tenantId
        }
    })
    let sql = `
        select seq,isInit, tenantId, locationId, invId, billNo, relId, billDate, transTypeName, transType, lastCount, lastPrice, lastMoney, unit, spec, calNum, unit1, unit2, inCount, inPrice, inMoney, outPrice, outCount, outMoney, costPrice, costMoney, iid, isCal,ctime,utime  
         from (
                        select 1 seq, 1 isInit, a.tenantId,a.locationId,a.invId,'' billNo,0 relId, '1970-01-01' billDate,N'期初' transTypeName,0  transType,isnull(ig.qty, 0) lastCount,
                                   case when ig.amount is not null then (case when isnull(ig.qty,0)=0 then 0 else ig.amount*1.0/ig.qty end) else isnull(a.price1*1.0/a.spec,0) end lastPrice,
                                   isnull(ig.amount,0) lastMoney,
                                   case when ig.unit is not null then ig.unit else (case when isnull(a.unit2,'')<>'' then a.unit2 else a.unit1 end) end unit,
                                   a.spec,1 calNum,a.unit1,a.unit2,0 inCount,0 inPrice,0 inMoney,0 outPrice,0 outCount,0 outMoney,0 costPrice,0 costMoney,0 iid,1 isCal,'${ut}' ctime,'${ut}' utime
                                       from (select a.*,a.id invId,b.id locationId,b.name locationName from goods a inner join storage b on a.tenantId=b.tenantId and a.tenantId=${tenantId}) as a
                                            left join init_goods as ig on a.tenantId=ig.tenantId and a.locationId=ig.locationId and a.invId=ig.invId
                        union all
                        select 0 seq, 0 isInit, a.tenantId, ii.locationId,ii.invId,a.billNo,ii.id relId,a.billDate,a.transTypeName,a.transType,0 lastCount ,0 lastPrice,0 lastMoney,
                                               case when isnull(c.unit2,'') = '' then c.unit1 else c.unit2 end unit,
                                               c.spec,ii.calNum,c.unit1,c.unit2,
                                               case when a.transType=150501 or a.transType=150706 then ii.qty*ii.calNum when a.transType=150502 then -ii.qty*ii.calNum else 0 end inCount,
                                               case when a.transType=150501 or a.transType=150706 then ii.amount*1.0/(ii.qty*ii.calNum) else 0 end inPrice,
                                               case when a.transType=150501 or a.transType=150706 then ii.amount else 0 end inMoney,
                                               case when a.transType=150601 or a.transType=150602 or a.transType=150603 or a.transType=150806 then  (case when (ii.calNum*ii.qty)=0 then 0 else ii.amount/(ii.calNum*ii.qty) end) else 0 end outPrice,
                                               case when a.transType=150601 or a.transType=150806 then ii.qty*ii.calNum when a.transType=150602 then -ii.qty*ii.calNum when a.transType=150603 and ii.trans=N'换入' then -ii.qty*ii.calNum
                                                    when a.transType=150603 and ii.trans=N'换出' then ii.qty*ii.calNum else 0 end outCount,
                                               case when a.transType=150601 or a.transType=150602 or a.transType=150603 or a.transType=150806 then  ii.amount else 0 end outMoney,
                                               0 costPrice,0 costMoney,a.id iid,case when a.billState='accounted' then 1 else 0 end isCal, '${ut}' ctime,'${ut}' utime 
                            from invoice as a
                            inner join invoice_info as ii on a.id=ii.iid
                            inner join goods as c on ii.invId=c.id
                            where a.tenantId=${tenantId} and a.billState='accounted' and a.billType in('PUR','OI','SALE','OO')
               ) a order by tenantId,locationId,invId,billDate,transType 
        `
    let list = await db.query(sql, {type: QueryTypes.SELECT})
    await saveInventory(list)
}
// 写入库存余额
const insertInventory = async (ctx,billId, t) => {
    let tenantId = ctx.user.tenantId
    let ut = tools.getTime()
    await db.models['inventoryDetail'].destroy({
        where: {
            tenantId,
            iid: billId
        }
    }, {transaction: t})
    // 写入明细
    let insertSql = `
                    insert into inventory_detail (tenantId, locationId, invId, billNo, relId, billDate, transTypeName, transType, lastCount,lastPrice, lastMoney, unit, spec, calNum, unit1, unit2, inCount,inPrice,inMoney,outPrice,outCount,outMoney,costPrice,costMoney,iid,isCal,ctime, utime)
                    select a.tenantId, ii.locationId,ii.invId,a.billNo,ii.id,a.billDate,a.transTypeName,a.transType,0,0,0,
                           case when isnull(c.unit2,'') = '' then c.unit1 else c.unit2 end,
                           c.spec,ii.calNum,c.unit1,c.unit2,
                           case when a.transType=150501 or a.transType=150706 then ii.qty*ii.calNum when a.transType=150502 then -ii.qty*ii.calNum else 0 end,
                           case when a.transType=150501 or a.transType=150706 then ii.amount*1.0/(ii.qty*ii.calNum) else 0 end,
                           case when a.transType=150501 or a.transType=150706 then ii.amount else 0 end,
                           case when a.transType=150601 or a.transType=150602 or a.transType=150603 or a.transType=150806 then  ii.amount/(ii.calNum*ii.qty) else 0 end,
                           case when a.transType=150601 or a.transType=150806 then ii.qty*ii.calNum when a.transType=150602 then -ii.qty*ii.calNum when a.transType=150603 and ii.trans=N'换入' then -ii.qty*ii.calNum
                                when a.transType=150603 and ii.trans=N'换出' then ii.qty*ii.calNum else 0 end,
                           case when a.transType=150601 or a.transType=150602 or a.transType=150603 or a.transType=150806 then  ii.amount else 0 end,
                           0,0,a.id,case when a.billState='accounted' then 1 else 0 end, '${ut}','${ut}'
                    from invoice as a
                             inner join invoice_info as ii on a.id=ii.iid
                             inner join goods as c on ii.invId=c.id
                    where a.tenantId=${tenantId} and a.id=${billId};
        `
    await db.query(insertSql, {type: QueryTypes.INSERT}, {transaction: t})
}
// 清除数据
const clearData = async (ctx) => {
    let tenantId = ctx.user.tenantId
    await db.transaction(async t => {
        await db.models['invoiceInfo'].destroy({
            where: {
                tenantId
            }
        }, {transaction: t})
        await db.models['invoice'].destroy({
            where: {
                tenantId
            }
        }, {transaction: t})
        await db.models['inventory'].destroy({
            where: {
                tenantId
            }
        }, {transaction: t})
        await db.models['inventoryDetail'].destroy({
            where: {
                tenantId
            }
        }, {transaction: t})
        await db.models['invoiceCs'].destroy({
            where: {
                tenantId
            }
        }, {transaction: t})
        await db.models['invoiceDeliver'].destroy({
            where: {
                tenantId
            }
        }, {transaction: t})
        await db.models['invoiceInfoYs'].destroy({
            where: {
                tenantId
            }
        }, {transaction: t})
        await db.models['invoiceLog'].destroy({
            where: {
                tenantId
            }
        }, {transaction: t})
        await db.models['invoiceOther'].destroy({
            where: {
                tenantId
            }
        }, {transaction: t})
        await db.models['invoiceReceipt'].destroy({
            where: {
                tenantId
            }
        }, {transaction: t})
        await db.models['invoiceTrans'].destroy({
            where: {
                tenantId
            }
        }, {transaction: t})
        await db.models['invoiceYsf'].destroy({
            where: {
                tenantId
            }
        }, {transaction: t})
        await db.models['invoiceBack'].destroy({
            where: {
                tenantId
            }
        }, {transaction: t})
    })
}
// 开始账期更新余额表期初
const initInventoryBeg = async (ctx) => {
    let tenantId = ctx.user.tenantId
    let ut = tools.getTime()
    await db.transaction(async t => {
        await db.models['inventory'].destroy({
            where: {
                tenantId
            }
        }, {transaction: t})
        await db.models['inventoryDetail'].destroy({
            where: {
                tenantId
            }
        }, {transaction: t})
        let sql = `
            merge into inventory_detail a using
            (
                select a.tenantId,a.locationId,a.invId,a.spec,a.unit2,a.unit1,isnull(ig.qty, 0) initQty,isnull(ig.amount,0) initAmount, 
                       case when ig.amount is not null then (case when isnull(ig.qty,0)=0 then 0 else ig.amount*1.0/ig.qty end) else isnull(a.price1*1.0/a.spec,0) end initPrice 
                       from (select a.*,a.id invId,b.id locationId,b.name locationName from goods a inner join storage b on a.tenantId=b.tenantId and a.tenantId=${tenantId}) a
                         left join init_goods ig on a.tenantId=ig.tenantId and a.locationId=ig.locationId and a.invId=ig.invId
            ) b
            on a.tenantId=b.tenantId and a.locationId=b.locationId and a.invId=b.invId
            when not matched then insert (tenantId,seq, locationId, invId, billNo, relId, billDate, transTypeName, transType, lastCount, lastPrice,lastMoney, unit, calNum, unit1, unit2, iid, ctime, utime,isCal)
            values (b.tenantId,1,b.locationId,b.invId,'',0,'1970-01-01',N'期初','',b.initQty,b.initPrice,initAmount,case when isnull(b.unit2,'')<>'' then b.unit2 else b.unit1 end,1,b.unit1,b.unit2,0, '${ut}','${ut}',1);
        `
        await db.query(sql, {type: QueryTypes.INSERT}, {transaction: t})
    })
}

module.exports = {updateInventoryByBill, updateInventoryByBeg, insertInventory, clearData, initInventoryBeg}